package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.Details;
import com.ysd.entity.FishClassify;
import com.ysd.entity.NewsDetails;
import com.ysd.util.DBManager;

public class FishDetailsDaoImpl {
	//是否有参数看sql语句是否有条件
		public List<Details> getDetailsList(int dId){//cId产品的编号
			List<Details> detailsList = new ArrayList<Details>();
			Connection conn = DBManager.getConnection();
			Statement st = null;
			ResultSet rs = null;
			
			try {
				st = conn.createStatement();
				rs = st.executeQuery("select * from details where fishClassifyId="+dId);
				while(rs.next()) {
					int fId = rs.getInt("fishId");
					String fName = rs.getString("fishName");
					String fPicture = rs.getString("fishPicture");
					Details details = new Details(fId, fName, fPicture);
					detailsList.add(details);
				}
				return detailsList;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			} finally {
				DBManager.closeAll(conn, st, rs);
			}
			
		}
		
	public List<Details> getFDetails(int cId){
		List<Details> FDList = new ArrayList<Details>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from details where fishId="+cId);
			while(rs.next()) {
				int fishId = rs.getInt("fishId");
				String fishName = rs.getString("fishName");
				String fishAdress = rs.getString("fishAdress");
				int fishPrice = rs.getInt("fishPrice");
				String fishLifeTime = rs.getString("fishLifeTime");
				String isFreeDelivery = rs.getString("isFreeDelivery");
				int fishClassifyId = rs.getInt("fishClassifyId");
				String fishPicture = rs.getString("fishPicture");
				Details fDetails = new Details(fishId, fishName, fishAdress, fishPrice, fishLifeTime, isFreeDelivery, fishClassifyId, fishPicture);
				FDList.add(fDetails);
			}
			return FDList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public List<FishClassify> getFCetails(int fId){
		List<FishClassify> FCList = new ArrayList<FishClassify>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from fishclassify where classifyId="+fId);
			while(rs.next()) {
				int classifyId = rs.getInt("classifyId");
				String fishClassifyName = rs.getString("fishClassifyName");
				FishClassify fishCList = new FishClassify(classifyId, fishClassifyName);
				FCList.add(fishCList);
			}
			return FCList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public List<Details> selDetails(){
		List<Details> FDList = new ArrayList<Details>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from details");
			while(rs.next()) {
				int fishId = rs.getInt("fishId");
				String fishName = rs.getString("fishName");
				String fishAdress = rs.getString("fishAdress");
				int fishPrice = rs.getInt("fishPrice");
				String fishLifeTime = rs.getString("fishLifeTime");
				String isFreeDelivery = rs.getString("isFreeDelivery");
				int fishClassifyId = rs.getInt("fishClassifyId");
				String fishPicture = rs.getString("fishPicture");
				Details fDetails = new Details(fishId, fishName, fishAdress, fishPrice, fishLifeTime, isFreeDelivery, fishClassifyId, fishPicture);
				FDList.add(fDetails);
			}
			return FDList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	/**
	 * 根据产品详情返回的Id删除产品
	 * @param id fishId
	 * @return 数据库受影响行数
	 */
	public int delProductDetails(int fid) {
		return DBManager.executeUpdate("delete from details where fishId="+fid);
	}
	
	/**
	 * 多条件查询产品信息
	 * @return 集合list
	 */
	public List<Details> selProductByWhere(Details d){
		
		List<Details> list = new ArrayList<Details>();
		
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			
			String sql = "select d.*,fc.fishClassifyName from details d,fishclassify fc where 1=1";
			if(d.getFishId()!=0) {
				sql += " and fishId=" + d.getFishId();
			}
			if(d.getFishName()!=null && !d.getFishName().equals("")) {
				sql += " and fishName like '%"+d.getFishName()+"%'";
			}
			if(d.getFishAdress()!=null && !d.getFishAdress().equals("")) {
				sql += " and fishAdress like '%"+d.getFishAdress()+"%'";
			}
			if(d.getBeginPrice()!=0) {
				sql += " and fishPrice>" + d.getBeginPrice();
			}
			if(d.getEndPrice()!=0) {
				sql += " and fishPrice<" + d.getEndPrice();
			}
			if(d.getFishLifeTime()!=null && !d.getFishLifeTime().equals("")) {
				sql += " and fishLifeTime like '%"+d.getFishLifeTime()+"%' ";
			}
			if(d.getIsFreeDelivery()!=null && !d.getIsFreeDelivery().equals("")) {
				sql += " and isFreeDelivery='"+d.getIsFreeDelivery()+"'";
			}
			if(d.getFishClassifyId()!=0) {
				sql += " and fishClassifyId=" + d.getFishClassifyId();
			}
			sql += " and d.fishClassifyId=fc.classifyId";
			System.out.println(sql);//打印
			
			//rs结果集
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int fishId = rs.getInt("fishId");
				String fishName = rs.getString("fishName");
				String fishAdress = rs.getString("fishAdress");
				int fishPrice = rs.getInt("fishPrice");
				String fishLifeTime = rs.getString("fishLifeTime");
				String isFreeDelivery = rs.getString("isFreeDelivery");
				int fishClassifyId = rs.getInt("fishClassifyId");
				String fishPicture = rs.getString("fishPicture");
				String fishClassifyName = rs.getString("fishClassifyName");
				Details det = new Details(fishId, fishName, fishAdress, fishPrice, 0, 0, fishLifeTime, isFreeDelivery, fishClassifyId, fishPicture, fishClassifyName);
				list.add(det);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();//打印异常信息
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	/**
	 * 多条件分页查询产品信息
	 * @return 集合list
	 */
	public List<Details> selProductByPage(Details d, int page, int size){
		//创建集合
		List<Details> list = new ArrayList<Details>();
		//创建连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {//正常
			//创建Statement对象
			st = conn.createStatement();
			//where 1=1 永真条件(永远为true)
			String sql = "select d.*,fc.fishClassifyName from details d,fishclassify fc where 1=1";
			if(d.getFishId()!=0) {
				sql += " and fishId=" + d.getFishId();
			}
			if(d.getFishName()!=null && !d.getFishName().equals("")) {
				sql += " and fishName like '%"+d.getFishName()+"%'";
			}
			if(d.getFishAdress()!=null && !d.getFishAdress().equals("")) {
				sql += " and fishAdress like '%"+d.getFishAdress()+"%'";
			}
			if(d.getBeginPrice()!=0) {
				sql += " and fishPrice>" + d.getBeginPrice();
			}
			if(d.getEndPrice()!=0) {
				sql += " and fishPrice<" + d.getEndPrice();
			}
			if(d.getFishLifeTime()!=null && !d.getFishLifeTime().equals("")) {
				sql += " and fishLifeTime like '%"+d.getFishLifeTime()+"%' ";
			}
			if(d.getIsFreeDelivery()!=null && !d.getIsFreeDelivery().equals("")) {
				sql += " and isFreeDelivery='"+d.getIsFreeDelivery()+"'";
			}
			if(d.getFishClassifyId()!=0) {
				sql += " and fishClassifyId=" + d.getFishClassifyId();
			}
			sql += " and d.fishClassifyId=fc.classifyId limit "+(page-1)*size+","+size;
			System.out.println(sql);//查看sql语句是否有拼接错误
			
			//rs结果集
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int fishId = rs.getInt("fishId");
				String fishName = rs.getString("fishName");
				String fishAdress = rs.getString("fishAdress");
				int fishPrice = rs.getInt("fishPrice");
				String fishLifeTime = rs.getString("fishLifeTime");
				String isFreeDelivery = rs.getString("isFreeDelivery");
				int fishClassifyId = rs.getInt("fishClassifyId");
				String fishPicture = rs.getString("fishPicture");
				String fishClassifyName = rs.getString("fishClassifyName");
				Details det = new Details(fishId, fishName, fishAdress, fishPrice, 0, 0, fishLifeTime, isFreeDelivery, fishClassifyId, fishPicture, fishClassifyName);
				list.add(det);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();//打印异常信息
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	
	/**
	 * 根据数据库的所有字段添加
	 * @param p
	 * @return 受影响行数
	 */
	public int addProductDetails(Details p) {
		return DBManager.executeUpdate("insert into details values(null,'" + p.getFishName() + "','" + p.getFishAdress()
						+ "'," + p.getFishPrice() + ",'" + p.getFishLifeTime() + "','" + p.getIsFreeDelivery() + "',"
						+ p.getFishClassifyId() + ",'" + p.getFishPicture() + "')");
	}
	
	/**
	 * 根据产品名称查询数据
	 * @param fName
	 * @return 集合
	 */
	public List<Details> getProDetailsByFName(String fName){
		List<Details> nsList = new ArrayList<Details>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from details where fishName='"+fName+"'"); 
					
			while(rs.next()) {
				int fishId = rs.getInt("fishId");
				String fishName = rs.getString("fishName");
				String fishAdress = rs.getString("fishAdress");
				int fishPrice = rs.getInt("fishPrice");
				String fishLifeTime = rs.getString("fishLifeTime");
				String isFreeDelivery = rs.getString("isFreeDelivery");
				int fishClassifyId = rs.getInt("fishClassifyId");
				String fishPicture = rs.getString("fishPicture");
				Details d = new Details(fishId, fishName, fishAdress, fishPrice, fishLifeTime, isFreeDelivery, fishClassifyId, fishPicture);
				nsList.add(d);
			}
			return nsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	/**
	 * 根据产品id查询数据
	 * @param fId
	 * @return
	 */
	public Details getProDetailsById(int fId) {
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		Details det = null;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("select * from details where fishId=" + fId);
			while(rs.next()) {
				int fishId = rs.getInt("fishId");
				String fishName = rs.getString("fishName");
				String fishAdress = rs.getString("fishAdress");
				int fishPrice = rs.getInt("fishPrice");
				String fishLifeTime = rs.getString("fishLifeTime");
				String isFreeDelivery = rs.getString("isFreeDelivery");
				int fishClassifyId = rs.getInt("fishClassifyId");
				String fishPicture = rs.getString("fishPicture");
				det = new Details(fishId, fishName, fishAdress, fishPrice, fishLifeTime, isFreeDelivery, fishClassifyId, fishPicture);
			}
			return det;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	/**
	 * 根据产品id修改产品
	 * @param d
	 * @return
	 */
	public int updateProductDetails(Details d) {
		String sql = "update details set fishName='"+d.getFishName()+"',fishAdress='"+d.getFishAdress()+"',fishPrice="+d.getFishPrice()+",fishLifeTime='"+d.getFishLifeTime()+"',isFreeDelivery='"+d.getIsFreeDelivery()+"',fishClassifyId="+d.getFishClassifyId()+",fishPicture='"+d.getFishPicture()+"' where fishId="+d.getFishId();
		System.out.println(sql);
		return DBManager.executeUpdate(sql);
	}
	
	/**
	 * 批量删除
	 * @param ids
	 * @return 多个id为String
	 */
	public int delProDetailsByIds(String ids) {
		String sql = "delete from details where fishId in("+ids+")";
		System.out.println(sql);
		return DBManager.executeUpdate(sql);
	}
	
}
